package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.Beans.CompetitionDetails;

@Repository (value="detaildao")
public class CompetitionDetailDAOImpl implements CompetitionDetailDAO{
	@Autowired
	private JdbcTemplate jt;


	@Override
	public List<CompetitionDetails> getAll(final int cid) {
		// TODO Auto-generated method stub
		return template(cid,"CALL DETAIL_GETALL(?);");
	}

	@Override
	public List<CompetitionDetails> getLastJoined(int cid) {
		// TODO Auto-generated method stub
		return template(cid,"CALL DETAIL_GETLASTJOINED(?);");
	}

	@Override
	public List<CompetitionDetails> getMostComment(int cid) {
		// TODO Auto-generated method stub
		return template(cid,"CALL DETAIL_GETMOSTCOMMENTED(?);");
	}

	@Override
	public List<CompetitionDetails> getMostViewed(int cid) {
		// TODO Auto-generated method stub
		return template(cid,"CALL DETAIL_GETMOSTVIEWED(?);");
	}

	@Override
	public List<CompetitionDetails> getMostVoted(int cid) {
		// TODO Auto-generated method stub
		return template(cid,"CALL DETAIL_GETMOSTVOTED(?);");
	}

	@Override
	public byte[] getCover(final int rid) {
		// TODO Auto-generated method stub
		if(rid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL DETAIL_GETCOVER(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, rid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									obj[1]=rs.getBytes("image");
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (byte[]) obj[1];
		}
		return null;
	}

	@Override
	public byte[] getCoverpreview(final int rid) {
		// TODO Auto-generated method stub
		if(rid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL DETAIL_GETCOVERPRE(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, rid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									obj[1]=rs.getBytes("preview");
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (byte[]) obj[1];
		}
		return null;
	}


	@Override
	public CompetitionDetails get(final int uid, final int cid) {
		// TODO Auto-generated method stub
		if((uid>0)&&(cid>0))
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL DETAIL_GET(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, uid);
							stm.setInt(2, cid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									CompetitionDetails detail=new CompetitionDetails();
									detail.setCharacter(rs.getString("character"));
									detail.setCommented(rs.getInt("commented"));
									detail.setCompetitionDescription(rs.getString("competitiondescription"));
									detail.setCompetitionID(rs.getInt("competitionid"));
									detail.setCompetitionName(rs.getString("competitionname"));
									detail.setCostume(rs.getString("costume"));
									detail.setDescription(rs.getString("description"));
									detail.setId(rs.getInt("id"));
									detail.setJoined(rs.getDate("joineddate"));
									detail.setLastviewed(rs.getTimestamp("lastviewed"));
									detail.setLastvoted(rs.getTimestamp("lastvoted"));
									detail.setNickname(rs.getString("nickname"));
									detail.setPhotographer(rs.getString("photographer"));
									detail.setType(rs.getString("type"));
									detail.setUserID(rs.getInt("userid"));
									detail.setUsername(rs.getString("username"));
									detail.setViewed(rs.getInt("viewed"));
									detail.setVoted(rs.getInt("voted"));
									obj[1]=detail;
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (CompetitionDetails) obj[1];
		}
		return null;
	}


	@Override
	public CompetitionDetails get(final int rid) {
		// TODO Auto-generated method stub
		if(rid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL DETAIL_GETSINGLE(?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, rid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								if (rs.next())
								{
									CompetitionDetails detail=new CompetitionDetails();
									detail.setCharacter(rs.getString("character"));
									detail.setCommented(rs.getInt("commented"));
									detail.setCompetitionDescription(rs.getString("competitiondescription"));
									detail.setCompetitionID(rs.getInt("competitionid"));
									detail.setCompetitionName(rs.getString("competitionname"));
									detail.setCostume(rs.getString("costume"));
									detail.setDescription(rs.getString("description"));
									detail.setId(rs.getInt("id"));
									detail.setJoined(rs.getDate("joineddate"));
									detail.setLastviewed(rs.getTimestamp("lastviewed"));
									detail.setLastvoted(rs.getTimestamp("lastvoted"));
									detail.setNickname(rs.getString("nickname"));
									detail.setPhotographer(rs.getString("photographer"));
									detail.setType(rs.getString("type"));
									detail.setUserID(rs.getInt("userid"));
									detail.setUsername(rs.getString("username"));
									detail.setViewed(rs.getInt("viewed"));
									detail.setVoted(rs.getInt("voted"));
									obj[1]=detail;
								}
								return obj;
							}});
			if(obj[1]!=null)
				return (CompetitionDetails) obj[1];
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	private List<CompetitionDetails> template (final int cid, final String sql)
	{
		if(cid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, cid);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=null;
								obj[1]=null;
								List<CompetitionDetails> details = new ArrayList<CompetitionDetails>();
								while (rs.next())
								{
									CompetitionDetails detail=new CompetitionDetails();
									detail.setCharacter(rs.getString("character"));
									detail.setCommented(rs.getInt("commented"));
									detail.setCompetitionDescription(rs.getString("competitiondescription"));
									detail.setCompetitionID(rs.getInt("competitionid"));
									detail.setCompetitionName(rs.getString("competitionname"));
									detail.setCostume(rs.getString("costume"));
									detail.setDescription(rs.getString("description"));
									detail.setId(rs.getInt("id"));
									detail.setJoined(rs.getDate("joineddate"));
									detail.setLastviewed(rs.getTimestamp("lastviewed"));
									detail.setLastvoted(rs.getTimestamp("lastvoted"));
									detail.setNickname(rs.getString("nickname"));
									detail.setPhotographer(rs.getString("photographer"));
									detail.setType(rs.getString("type"));
									detail.setUserID(rs.getInt("userid"));
									detail.setUsername(rs.getString("username"));
									detail.setViewed(rs.getInt("viewed"));
									detail.setVoted(rs.getInt("voted"));
									details.add(detail);
								}
								if (details.size()>0)
									obj[1]=details;
								return obj;
							}});
			if(obj[1]!=null)
				return (List<CompetitionDetails>) obj[1];
		}
		return null;
	}


	@Override
	public boolean edit(final int uid,final int cid,final CompetitionDetails detail) {
		// TODO Auto-generated method stub
		if ((uid>0)&(cid>0))
		{

			jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL DETAIL_EDIT(?,?,?,?,?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, uid);	
							stm.setInt(2, cid);
							stm.setString(3, detail.getCharacter());
							stm.setString(4, detail.getCostume());
							stm.setString(5, detail.getPhotographer());
							stm.setString(6, detail.getType());
							stm.setString(7, detail.getDescription());
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								stm.execute();
								return null;
							}});
			return true;
		}
		return false;
	}
}
